package org.mch.func.db.tool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.mch.comm.db.handler.ArrayStringHandler;
import org.mch.func.cons.ConsDB;
import org.mch.func.entity.TableColumnEO;

/**
 * 描述：数据库信息工具包；<BR/>
 * 
 * @author MCH；Date：2008-06-18；<BR/>
 *
 */
public class DBInfoUtil {

	/**
	 * 根据表名，获得表所有列信息；
	 * 
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, TableColumnEO> getColmnTypes(String table) throws SQLException {
		Map<String, TableColumnEO> typeMap = new HashMap<String, TableColumnEO>();
		ResultSet rs = null;

		if (ConsDB.connInfo.getDbtype() == ConsDB.MSSQLServer2005)
			rs = ConsDB.dbmd.getColumns(null, null, table.toUpperCase(), "%");
		else if (ConsDB.connInfo.getDbtype() == ConsDB.PostgreSQL8) {
			rs = ConsDB.dbmd.getColumns(null, null, table.toLowerCase(), "%");
		} else {
			rs = ConsDB.dbmd.getColumns(null, getSchema(), table.toUpperCase(), "%");
		}

		while (rs.next()) {
			TableColumnEO colinfo = new TableColumnEO();
			colinfo.name = rs.getString("COLUMN_NAME").toLowerCase();
			colinfo.coltype = rs.getShort("DATA_TYPE");

			colinfo.precision = rs.getInt("COLUMN_SIZE");
			colinfo.scale = rs.getMetaData().getScale(1);
			typeMap.put(colinfo.name, colinfo);
		}

		return typeMap;
	}

	public static String getSchema() {
		String strSche = null;
		try {
			String schema = ConsDB.dbmd.getUserName();
			switch (ConsDB.connInfo.getDbtype()) {
				case ConsDB.MSSQLServer2005:
					strSche = "dbo";
					break;
				case ConsDB.Oracle10g:
				case ConsDB.DB2: {
					if (schema == null || schema.length() == 0)
						throw new IllegalArgumentException("ORACLE");
					strSche = schema.toUpperCase();
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strSche;
	}

	public static String getTablePKField(String tablename) throws Exception {
		ResultSet rs = ConsDB.dbmd.getPrimaryKeys(null, null, tablename);
		boolean temp = rs.next();
		if (temp) {
			return rs.getString("COLUMN_NAME").toLowerCase();
		}
		return null;
	}

	/**
	 * 根据数据库连接，返回当前数据库系统类型；
	 */
	public static int getDBMSType(Connection con) throws SQLException {
		String dbName = null;
		int dbType = -1;

		dbName = con.getMetaData().getDatabaseProductName();
		dbName = dbName.toLowerCase();
		if (-1 != dbName.indexOf("sql server"))
			dbType = 3;
		else if (-1 != dbName.indexOf("oracle"))
			dbType = 0;
		else if (dbName.indexOf("db2") != -1)
			dbType = 1;
		else if (dbName.indexOf("hsql") != -1)
			dbType = 9;
		else if (dbName.indexOf("postgresql") != -1)
			dbType = 5;

		return dbType;
	}

	/**
	 * 获得当前数据库连接下的所有用户表；
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static List<String> getUserTables() throws SQLException {
		QueryRunner run = new QueryRunner();

		if (ConsDB.connInfo.getDbtype() == ConsDB.MSSQLServer2000) {
			String sql = "select name from sysobjects where type ='U' order by name";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.MSSQLServer2005) {
			String sql = "select name from sysobjects where type ='U' order by name";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.Oracle10g) {
			String sql = "select table_name from user_tables";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.MySQL5) {
			String sql = "SELECT TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=\""
					+ ConsDB.connInfo.getDatabasename() + "\" ORDER BY TABLE_NAME";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.PostgreSQL8) {
			String sql = "SELECT tablename FROM pg_tables";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else {
			return null;
		}
	}

	/**
	 * 模糊获取用户表；
	 * 
	 * @param tablename
	 * @return
	 * @throws SQLException
	 */
	public static List<String> getUserTables(String tablename) throws SQLException {
		QueryRunner run = new QueryRunner();

		if (ConsDB.connInfo.getDbtype() == ConsDB.MSSQLServer2000) {
			String sql = "select name from sysobjects where type ='U' and name like '%" + tablename + "%' order by name";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.MSSQLServer2005) {
			String sql = "select name from sysobjects where name like '%" + tablename + "%' and type ='U' order by name";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.Oracle10g) {
			String sql = "select table_name from user_tables where table_name like '%" + tablename + "%'";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.MySQL5) {
			ResultSet rs = ConsDB.conn.getMetaData().getTables(ConsDB.connInfo.getDatabasename(), null, null, null);
			List<String> list = new ArrayList<String>();
			while (rs.next()) {
				String name = (String) rs.getObject("table_name");
				if (tablename == null || tablename.trim().equals("")) {
					list.add(name);
				} else {
					if (name.indexOf(tablename) >= 0) {
						list.add(name);
					}
				}
			}
			return list;

		} else if (ConsDB.connInfo.getDbtype() == ConsDB.PostgreSQL8) {
			String sql = "SELECT tablename FROM pg_tables where tablename like '%" + tablename + "%'";
			return run.query(ConsDB.conn, sql, new ArrayStringHandler());

		} else {
			return null;
		}
	}
}
